import sqlite3
import time
import os

conn = sqlite3.connect('outbreak-management-system.db')
c = conn.cursor()
print ("Opened database successfully")
c = conn.cursor()

# 创建楼道管理者表
c.execute('''CREATE TABLE ADMINISTRATOR
       (ID          VARCHAR(18) PRIMARY KEY    NOT NULL,
        USERNAME    VARCHAR(20) UNIQUE         NOT NULL,
        PASSWORD    VARCHAR(256)                NOT NULL,
        NAME        VARCHAR(50)                NOT NULL,      
        CORRIDORNUMBER  VARCHAR(10)            NOT NULL,
        TELEPHONE   VARCHAR(11)                NOT NULL                            
        );''')

# 创建住户表 ID身份证号 PASSWORD密码 NAME姓名 AGE年龄 SEX性别 ROOMNUMBER门牌号 CORRIDORNUMBER楼栋号 TELEPHONE电话号码
# ADMINID 对应楼道管理者
c.execute('''CREATE TABLE PROPRIETOR
       (ID          VARCHAR(18) PRIMARY KEY    NOT NULL,
        USERNAME    VARCHAR(20) UNIQUE         NOT NULL,
        PASSWORD    VARCHAR(256)                NOT NULL,
        NAME        VARCHAR(50)                NOT NULL,
        AGE         INTEGER                    NOT NULL,
        SEX         INTEGER                    NOT NULL,
        ROOMNUMBER  VARCHAR(10)                NOT NULL,
        CORRIDORNUMBER  VARCHAR(10)            NOT NULL,
        TELEPHONE   VARCHAR(11)                NOT NULL,
        ADMINID     VARCHAR(18) REFERENCES ADMINISTRATOR(ID),
        CHECK (SEX IN (0, 1))
        );''')
# c.execute("INSERT INTO PEOPLE (ID,PASSWORD,NAME,POSITION) VALUES('root','root','root',0)")

# 创建访客信息表
c.execute('''CREATE TABLE VISITOR
       (ID          VARCHAR(18) PRIMARY KEY    NOT NULL,
        NAME        VARCHAR(50)                NOT NULL,
        AGE         INTEGER                    NOT NULL,
        SEX         INTEGER                    NOT NULL CHECK (SEX IN (0, 1)),
        TELEPHONE   VARCHAR(11)                NOT NULL
        CHECK (AGE > 0 AND AGE <150),
        CHECK (SEX IN (0,1))
        );''')


# 创建每日健康表 表单序号 感染状况(0未感染 1感染未痊愈 2感染已痊愈) 就医状况 体温 填写日期 
# 居住状况(0居住 1未居住)
c.execute('''CREATE TABLE HEALTHTABLE
       (FORMNO      VARCHAR(32) PRIMARY KEY    NOT NULL,
        INFECTSTATUS    INTEGER            NOT NULL,
        MEDICALCONDITION    VARCHAR(50)                ,      
        TEMPERATURE FLOAT                 NOT NULL,
        FILLINGTIME DATE                    NOT NULL,
        LIVINGSTATUS    INTEGER             NOT NULL,
        ADMINID     VARCHAR(18) REFERENCES ADMINISTRATOR(ID),
        PID         VARCHAR(18) REFERENCES PROPRIETOR(ID),
        CHECK (INFECTSTATUS IN (0, 1, 2, 3, 4, 5, 6)),
        CHECK (LIVINGSTATUS IN (0, 1))
        );''')

# 进出登记表 
c.execute('''CREATE TABLE IOTABLE(
        FORMNO      VARCHAR(32) PRIMARY KEY     NOT NULL,
        IDENTITY    INTEGER                     NOT NULL,
        TEMPERATURE FLOAT                       NOT NULL,
        HEALTHCODE  INTEGER                     NOT NULL,
        ROOMNUMBER  VARCHAR(10)                 NOT NULL,
        CORRIDORNUMBER  VARCHAR(10)             NOT NULL,
        FILLINGTIME DATE                        NOT NULL,
        IOSTATUS    INTEGER                     NOT NULL,
        IOREASON    VARCHAR(100)                NOT NULL,
        ADMINID     VARCHAR(18) REFERENCES ADMINISTRATOR(ID),
        CHECK (IDENTITY IN (0, 1)),
        CHECK (IOSTATUS IN (0, 1)),
        CHECK (HEALTHCODE IN (0, 1, 2))
);''')

# 用户进出填写情况表
c.execute('''CREATE TABLE PROPRIETORIOFILL(
        FORMNO      VARCHAR(10) REFERENCES IOTABLE(FORMNO),
        PID         VARCHAR(18) REFERENCES PROPRIETOR(ID),
        PRIMARY KEY(FORMNO, PID)
);''')

# 外来者进出填写情况表
c.execute('''CREATE TABLE VISITORIOFILL(
        FORMNO      VARCHAR(10) REFERENCES IOTABLE(FORMNO),
        PID         VARCHAR(18) REFERENCES VISITOR(ID),
        PRIMARY KEY(FORMNO, PID)
);''')

print ("Table created successfully")
conn.commit()
conn.close()